package merlin.dao;

import java.util.*;
import java.sql.*;

import merlin.beans.*;

public class LookupDetailsDAO implements java.io.Serializable{
	Connection con;

	public LookupDetailsDAO(Connection con){
		this.con = con;
	}

	public LookupDetailsBean selectLookupDetailsByPK(String pk)throws SQLException{
		LookupDetailsBean bn = null;
		PreparedStatement pstmt = con.prepareStatement("SELECT COMPANY_NUMBER,COMPANY_NAME,ADDRESS_LINE1,ADDRESS_LINE2,TOWN,COUNTY,POSTCODE,BUSINESS_DESCRIPTION FROM LOOKUP_DETAILS WHERE COMPANY_NUMBER = ? ");
		pstmt.setString(1,pk);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			bn = new LookupDetailsBean();
			bn.setCompanyNumber(rs.getString("COMPANY_NUMBER"));
			bn.setCompanyName(rs.getString("COMPANY_NAME"));
			bn.setAddressLine1(rs.getString("ADDRESS_LINE1"));
			bn.setAddressLine2(rs.getString("ADDRESS_LINE2"));
			bn.setTown(rs.getString("TOWN"));
			bn.setCounty(rs.getString("COUNTY"));
			bn.setPostcode(rs.getString("POSTCODE"));
			bn.setBusinessDescription(rs.getString("BUSINESS_DESCRIPTION"));
		}
		rs.close();
		pstmt.close();
		return bn;
	}

	public DbResultSet selectTopLookupDetailsByKeywordRecords(String whereClause,String orderByClause,int maxRecords)throws SQLException{
		ArrayList records = new ArrayList();
		String sql ="SELECT TOP " + maxRecords + " COMPANY_NUMBER,COMPANY_NAME FROM LOOKUP_DETAILS";
		if(whereClause !=null && !whereClause.equals(""))sql += " WHERE " + whereClause;
		if(orderByClause !=null && !orderByClause.equals(""))sql += " ORDER BY " + orderByClause;

		System.out.println("\n\nBy Keyword Query : " + sql + "\n\n ");

		Statement stmt =  con.createStatement();
		ResultSet rs =  stmt.executeQuery(sql);
		while(rs.next()){
			LookupDetailsBean bn = new LookupDetailsBean();
			bn.setCompanyNumber(rs.getString("COMPANY_NUMBER"));
			bn.setCompanyName(rs.getString("COMPANY_NAME"));
			records.add(bn);
		}
		rs.close();
		stmt.close();
		if(!records.isEmpty())return new DbResultSet(records,records.size()); else return new DbResultSet();
	}

	public DbResultSet selectLookupDetailsRecords(String whereClause,String orderByClause)throws SQLException{
		ArrayList records = new ArrayList();
		String sql ="SELECT COMPANY_NUMBER,COMPANY_NAME,ALPHAKEY_COMPANY_NAME,ADDRESS_LINE1,ADDRESS_LINE2,TOWN,COUNTY,POSTCODE,ALPHAKEY_POSTCODE,BUSINESS_DESCRIPTION,COMPANY_DATA FROM LOOKUP_DETAILS";
		if(whereClause !=null && !whereClause.equals(""))sql += " WHERE " + whereClause;
		if(orderByClause !=null && !orderByClause.equals(""))sql += " ORDER BY " + orderByClause;
		Statement stmt =  con.createStatement();
		ResultSet rs =  stmt.executeQuery(sql);
		while(rs.next()){
			LookupDetailsBean bn = new LookupDetailsBean(rs.getString("COMPANY_NUMBER"),rs.getString("COMPANY_NAME"),rs.getString("ALPHAKEY_COMPANY_NAME"),rs.getString("ADDRESS_LINE1"),rs.getString("ADDRESS_LINE2"),rs.getString("TOWN"),rs.getString("COUNTY"),rs.getString("POSTCODE"),rs.getString("ALPHAKEY_POSTCODE"),rs.getString("BUSINESS_DESCRIPTION"),rs.getString("COMPANY_DATA"));
			records.add(bn);
		}
		rs.close();
		stmt.close();
		if(!records.isEmpty())return new DbResultSet(records,records.size()); else return new DbResultSet();
	}

	public DbResultSet selectLookupDetailsRecordsByDirector(String director)throws SQLException{
		// The "director" string is in the form <firstname>|<surname>|<dob>
		String[] stTokens = director.split("\\|");
		// There are cases where the input string doesn't have all the three components. In such cases, return empty result.
		if(stTokens.length != 3){
			return new DbResultSet();
		}
		
    	String firstName = stTokens[0];
    	String surName   = stTokens[1];
    	String dob		 = stTokens[2];

		ArrayList records = new ArrayList();
      	String strSQL = "SELECT DISTINCT LD.COMPANY_NUMBER, LD.COMPANY_NAME FROM DIRECTOR_DETAILS DD JOIN LOOKUP_DETAILS LD ON LD.COMPANY_NUMBER = DD.COMPANY_NUMBER " +
		  				" WHERE DD.DIRECTOR_FIRST_NAME = ? AND DD.DIRECTOR_SURNAME = ? AND DD.DIRECTOR_DATE_OF_BIRTH = ?";
		PreparedStatement pstmt =  con.prepareStatement(strSQL);
		pstmt.setString(1,firstName);
		pstmt.setString(2,surName);
		pstmt.setString(3,dob);

		ResultSet rs =  pstmt.executeQuery();
		while(rs.next()){
			LookupDetailsBean bn = new LookupDetailsBean();
			bn.setCompanyNumber(rs.getString("COMPANY_NUMBER"));
			bn.setCompanyName(rs.getString("COMPANY_NAME"));
			records.add(bn);
		}
		rs.close();
		pstmt.close();
		if(!records.isEmpty())return new DbResultSet(records,records.size()); else return new DbResultSet();
	}

	public ArrayList selectCompanyNumberAndNameByPK(String strCompanyIDs)throws SQLException{
		ArrayList list = new ArrayList();

		String sql = "SELECT COMPANY_NUMBER ,COMPANY_NAME FROM LOOKUP_DETAILS WHERE COMPANY_NUMBER IN(" + strCompanyIDs + ") ORDER BY COMPANY_NAME";
		System.out.println(sql);
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()){
			String compDetails[] = {rs.getString("COMPANY_NUMBER"),rs.getString("COMPANY_NAME")};
			list.add(compDetails) ;
		}
		rs.close();
		stmt.close();

		return list;
	}
	
	public String selectCompanyNameByPK(String companyID)throws SQLException{
		String sql = "SELECT COMPANY_NAME FROM LOOKUP_DETAILS WHERE COMPANY_NUMBER = ?";
		String companyName = null;
		PreparedStatement stmt = con.prepareStatement(sql);
		stmt.setString(1, companyID);
		ResultSet rs = stmt.executeQuery();
		if(rs.next()){
			companyName = rs.getString(1);
		}
		rs.close();
		stmt.close();

		return companyName;
	}
	
	public List<String> getCompanyIdsInBibMailingListExclusionsList(String userCompanyNumber)throws SQLException{
		String sql = "SELECT BIBMLE_ORG FROM BIB_MAILING_LIST_EXCLUSION WHERE BIBMLE_ID = ?";
		List<String> companyIds = new LinkedList<String>();
		
		PreparedStatement stmt = con.prepareStatement(sql);
		stmt.setString(1, userCompanyNumber);
		ResultSet rs = stmt.executeQuery();
		while(rs.next()){
			companyIds.add(rs.getString(1));
		}
		rs.close();
		stmt.close();
		
		return companyIds;
	}
	
	public List<LookupDetailsBean> getCompaniesInBibMailingListExclusionsList(String userCompanyNumber)throws SQLException{
		String sql = "SELECT DISTINCT COMPANY_NUMBER, COMPANY_NAME " +
				"FROM LOOKUP_DETAILS, BIB_MAILING_LIST_EXCLUSION " +
				"WHERE BIBMLE_ORG = COMPANY_NUMBER AND BIBMLE_ID = ?";
		List<LookupDetailsBean> companies = new LinkedList<LookupDetailsBean>();
		
		PreparedStatement stmt = con.prepareStatement(sql);
		stmt.setString(1, userCompanyNumber);
		ResultSet rs = stmt.executeQuery();
		while(rs.next()){
			LookupDetailsBean bean = new LookupDetailsBean(rs.getString(1), rs.getString(2));
			companies.add(bean);
		}
		rs.close();
		stmt.close();
		
		return companies;
	}

	public int deleteMailingListExclusionList(String userCompanyNumber)throws SQLException{
		int updateCount = 0;
		String sql = "DELETE FROM BIB_MAILING_LIST_EXCLUSION WHERE BIBMLE_ID = ?";
		
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, userCompanyNumber);
		updateCount = pstmt.executeUpdate();
		pstmt.close();
		
		return updateCount;
	}
	
	public int[] addCompaniesToMailingListExclusionList(List<String> companyIds, String userCompanyNumber)throws SQLException{
		int[] updateCounts = null;
		String sql = "INSERT INTO BIB_MAILING_LIST_EXCLUSION(BIBMLE_ID, BIBMLE_ORG) VALUES(?, ?)";
		
		PreparedStatement pstmt = con.prepareStatement(sql);
		
		for(String companyId : companyIds){
			pstmt.setString(1,userCompanyNumber);
			pstmt.setString(2,companyId);
			pstmt.addBatch();
			pstmt.clearParameters();
		}
		updateCounts = pstmt.executeBatch();
		pstmt.close();
		return updateCounts;
	}
	
	public List<String> selectCompaniesInFinancialDistress(List<String> companyIds)throws SQLException{
		List<String> companyIdsInFinancialDistress = new LinkedList<String>();
		
		String compIds = "";
		for(int i = 0; i < companyIds.size() - 1; i++){
			compIds += "'" + companyIds.get(i) + "',";
		}
		compIds = compIds + "'" + companyIds.get(companyIds.size() - 1) + "'";
		
		String sql = "SELECT COMPANY_NUMBER FROM LOOKUP_DETAILS WHERE COMPANY_NUMBER IN (" + compIds + ") AND EXCLUDE_MAILING_RECORD='1'";
		
		System.out.println("selectCompaniesInFinancialDistress : " + sql);
		
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		
		while(rs.next()){
			companyIdsInFinancialDistress.add(rs.getString(1));
		}
		rs.close();
		stmt.close();
		
		return companyIdsInFinancialDistress;
	}
	
}